package dao;

import classes.CargoComissao;
import classes.CargoEfetivo;
import conexao.GerenciadorConexao;
import erro.ConexaoException;
import erro.ConsultaException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author Roberta
 */
public class CargoDao {

    private GerenciadorConexao g;

    public CargoDao() {
        g = GerenciadorConexao.getInstancia();
    }

    /**
     * 
     * @param ce CargoEfetivo
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public void inserir(CargoEfetivo ce) throws ConexaoException, ConsultaException {
        String sql = "INSERT INTO cargoefetivo (nome) VALUES (?)";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setString(1, ce.getNome());
            pstm.executeUpdate();

        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public List<CargoEfetivo> listarCE() throws ConexaoException, ConsultaException {
        List<CargoEfetivo> lista = new ArrayList<CargoEfetivo>();
        CargoEfetivo ce;
        String sql = "SELECT * FROM cargoefetivo ORDER BY nome";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                ce = new CargoEfetivo();
                ce.setCargoE_id(rs.getInt("idcargoEfetivo"));
                ce.setNome(rs.getString("nome"));
                lista.add(ce);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @param cc = CargoComissao
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public void inserir(CargoComissao cc) throws ConexaoException, ConsultaException {
        String sql = "INSERT INTO cargocomissao (nome) VALUES (?)";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setString(1, cc.getNome());
            pstm.executeUpdate();

        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public List<CargoComissao> listarCC() throws ConexaoException, ConsultaException {
        List<CargoComissao> lista = new ArrayList<CargoComissao>();
        CargoComissao cc;
        String sql = "SELECT * FROM cargocomissao ORDER BY nome";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                cc = new CargoComissao();
                cc.setCargoC_id(rs.getInt("idcargoComissao"));
                cc.setNome(rs.getString("nome"));
                lista.add(cc);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @param ce = CargoEfetivo
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public CargoEfetivo pesquisarCe(CargoEfetivo ce) throws ConexaoException, ConsultaException {
        String sql = "SELECT * FROM cargoefetivo WHERE idcargoEfetivo=?";
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, ce.getCargoE_id());
            ResultSet rs = pstm.executeQuery();
            if (rs.next()) {
                ce.setCargoE_id(rs.getInt("idcargoEfetivo"));
                ce.setNome(rs.getString("nome"));
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return ce;
    }

    /**
     * 
     * @param cc = CargoComissao
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public CargoComissao pesquisarCc(CargoComissao cc) throws ConexaoException, ConsultaException {
        String sql = "SELECT * FROM cargocomissao WHERE idcargoComissao=?";
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, cc.getCargoC_id());
            ResultSet rs = pstm.executeQuery();
            if (rs.next()) {
                cc.setCargoC_id(rs.getInt("idcargoComissao"));
                cc.setNome(rs.getString("nome"));
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return cc;
    }

    /**
     * 
     * @param cc = CargoComissao
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public void deletar(CargoComissao cc) throws ConexaoException, ConsultaException {

        String sql = "DELETE FROM cargocomissao WHERE idcargoComissao=?";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setInt(1, cc.getCargoC_id());
            pstm.executeUpdate();

        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Cargo em uso!");
        } finally {
            g.disconnect();
        }
    }

    /**
     * 
     * @param ce = CargoEfetivo
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public void deletar(CargoEfetivo ce) throws ConexaoException, ConsultaException {

        String sql = "DELETE FROM cargoefetivo WHERE idcargoEfetivo=?";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setInt(1, ce.getCargoE_id());
            pstm.executeUpdate();

        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Cargo em uso!");
        } finally {
            g.disconnect();
        }
    }

    /**
     * 
     * @param cc = CargoComissao
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public void alterarCc(CargoComissao cc) throws ConexaoException, ConsultaException {
        String sql = "UPDATE cargocomissao SET nome=? WHERE idcargoComissao=?";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setString(1, cc.getNome());

            pstm.setInt(2, cc.getCargoC_id());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    /**
     * 
     * @param ce = CargoEfetivo
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public void alterarCe(CargoEfetivo ce) throws ConexaoException, ConsultaException {
        String sql = "UPDATE cargoefetivo SET nome=? WHERE idcargoEfetivo=?";
        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setString(1, ce.getNome());

            pstm.setInt(2, ce.getCargoE_id());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }
}
